﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace LMKJ.Classes
{
    public class DatabaseManager
    {
        // Create
        public static int RegisterAccount(SocialWorker sw)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO SocialWorker(Username, Password, Name, Dob, Gender, Email, Joined_date)" +
                    "VALUES(@user, @pass, @name, @dob, @gender, @email, @joined_date)";

                comm.Parameters.AddWithValue("@user", sw.Username);
                comm.Parameters.AddWithValue("@pass", sw.Password);
                comm.Parameters.AddWithValue("@name", sw.Name);
                comm.Parameters.AddWithValue("@dob", sw.Dob);
                comm.Parameters.AddWithValue("@gender", sw.Gender);
                comm.Parameters.AddWithValue("@email", sw.Email);
                comm.Parameters.AddWithValue("@joined_date", DateTime.Now);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        public static int CreateResident(Resident r)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Resident(Nric, Name, Dob, Gender, Address, Postal, Ethnicity, Description)" +
                    "VALUES (@nric, @name, @dob, @gender, @address, @postal, @ethnicity, @description)";

                comm.Parameters.AddWithValue("@nric", r.Nric);
                comm.Parameters.AddWithValue("@name", r.Name);
                comm.Parameters.AddWithValue("@dob", r.Dob);
                comm.Parameters.AddWithValue("@gender", r.Gender);
                comm.Parameters.AddWithValue("@postal", r.Postal);
                comm.Parameters.AddWithValue("@address", r.Address);
                comm.Parameters.AddWithValue("@ethnicity", r.Ethnicity);
                comm.Parameters.AddWithValue("@description", r.Description);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        public static int CreateShoppingList(ShoppingList sl)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO ShoppingList(Nric, Date, Date_req, Status)" +
                    "VALUES (@nric, @date, @date_req, @status)";

                comm.Parameters.AddWithValue("@nric", sl.Nric);
                comm.Parameters.AddWithValue("@date", DateTime.Now);
                comm.Parameters.AddWithValue("@date_req", sl.Date_req);
                comm.Parameters.AddWithValue("@status", "Pending");

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        public static int CreateVolunteer(Volunteer v)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Volunteer(Nric, Name, Dob, Gender, Address, Postal, Telephone, Email, Available_day, Description)" +
                    "VALUES (@nric, @name, @dob, @gender, @address, @postal, @telephone, @email, @available_day, @description)";

                comm.Parameters.AddWithValue("@nric", v.Nric);
                comm.Parameters.AddWithValue("@name", v.Name);
                comm.Parameters.AddWithValue("@dob", v.Dob);
                comm.Parameters.AddWithValue("@gender", v.Gender);
                comm.Parameters.AddWithValue("@address", v.Address);
                comm.Parameters.AddWithValue("@postal", v.Postal);
                comm.Parameters.AddWithValue("@telephone", v.Telephone);
                comm.Parameters.AddWithValue("@email", v.Email);
                comm.Parameters.AddWithValue("@available_day", v.Available_day);
                comm.Parameters.AddWithValue("@description", v.Description);

                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        public static int CreateItemList(ItemList il)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO ItemList(List_id, Item_name, Qty)" +
                    " VALUES(@lid, @item_name, @qty)";

                comm.Parameters.AddWithValue("@lid", il.List_id);
                comm.Parameters.AddWithValue("@item_name", il.Item_name);
                comm.Parameters.AddWithValue("@qty", il.Qty);
                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        public static int CreateItem(Item i)
        {
            int rowadded = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Item(Item_name, Description, Price)" +
                    " VALUES(@item_name, @description, @price)";

                comm.Parameters.AddWithValue("@item_name", i.Item_name);
                comm.Parameters.AddWithValue("@description", i.Description);
                comm.Parameters.AddWithValue("@price", i.Price);
                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }


        // Retrieve (All)
        public static ArrayList RetrieveAllResident()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Resident";

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Resident r = new Resident();
                    r.Nric = (string)dr["Nric"];
                    r.Name = (string)dr["Name"];
                    DateTime dobDT = (DateTime)dr["Dob"];
                    r.Dob = dobDT.ToShortDateString();
                    r.Gender = (string)dr["Gender"];
                    r.Address = (string)dr["Address"];
                    r.Ethnicity = (string)dr["Ethnicity"];
                    if (DBNull.Value != (object)dr["Description"])
                    {
                        r.Description = (string)dr["Description"];
                    }

                    result.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ArrayList RetrieveAllShoppingList()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM ShoppingList";

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    ShoppingList sl = new ShoppingList();
                    sl.List_id = (int)dr["List_ID"];
                    sl.Nric = (string)dr["Nric"];
                    if (DBNull.Value != (object)dr["Volunteer_ID"])
                    {
                        sl.Volunteer_id = (int)dr["Volunteer_ID"];
                    }
                    DateTime dateDT = (DateTime)dr["Date"];
                    sl.Date = dateDT.ToShortDateString();
                    dateDT = (DateTime)dr["Date_Req"];
                    sl.Date_req = dateDT.ToShortDateString();
                    if (DBNull.Value != (object)dr["Date_Pur"])
                    {
                        dateDT = (DateTime)dr["Date_Pur"];
                        sl.Date_pur = dateDT.ToShortDateString();
                    }
                    if (DBNull.Value != (object)dr["Date_Del"])
                    {
                        dateDT = (DateTime)dr["Date_Del"];
                        sl.Date_del = dateDT.ToShortDateString();
                    }
                    sl.Status = (string)dr["Status"];

                    result.Add(sl);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ArrayList RetrieveAllVolunteer()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Volunteer";

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Volunteer v = new Volunteer();
                    v.Volunteer_id = (int)dr["Volunteer_ID"];
                    v.Nric = (string)dr["Nric"];
                    v.Name = (string)dr["Name"];
                    DateTime dobDT = (DateTime)dr["Dob"];
                    v.Dob = dobDT.ToShortDateString();
                    v.Gender = (string)dr["Gender"];
                    v.Address = (string)dr["Address"];
                    v.Telephone = (string)dr["Telephone"];
                    v.Email = (string)dr["Email"];
                    if (DBNull.Value != (object)dr["Available_day"])
                    {
                        v.Available_day = (string)dr["Available_day"];
                    }
                    if (DBNull.Value != (object)dr["Description"])
                    {
                        v.Description = (string)dr["Description"];
                    }

                    result.Add(v);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ArrayList RetrieveAllItem()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Item";

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Item i = new Item();
                    i.Item_name = (string)dr["Item_name"];
                    i.Price = Convert.ToDouble(dr["Price"].ToString());
                    if (DBNull.Value != (object)dr["Description"])
                    {
                        i.Description = (string)dr["Description"];
                    }

                    result.Add(i);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ArrayList RetrieveAllItemName()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Item";

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    result.Add((string)dr["Item_name"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }


        // Retrieve (Search)
        public static Resident RetrieveResidentByNric(string n)
        {
            Resident r = new Resident();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Resident WHERE Nric=@nric";
                comm.Parameters.AddWithValue("@nric", n);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    r.Nric = n;
                    r.Name = (string)dr["Name"];
                    DateTime dobDT = (DateTime)dr["Dob"];
                    r.Dob = dobDT.Day + "-" + dobDT.Month + "-" + dobDT.Year;
                    r.Gender = (string)dr["Gender"];
                    r.Address = (string)dr["Address"];
                    r.Postal = (string)dr["Postal"];
                    r.Ethnicity = (string)dr["Ethnicity"];
                    if (DBNull.Value != (object)dr["Description"])
                    {
                        r.Description = (string)dr["Description"];
                    }
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return r;
        }

        public static ArrayList RetrieveResidentsByName(string n)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Resident WHERE Name LIKE @name";
                comm.Parameters.AddWithValue("@name", "%" + n + "%");

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Resident r = new Resident();
                    r.Nric = (string)dr["Nric"];
                    r.Name = (string)dr["Name"];
                    DateTime dobDT = (DateTime)dr["Dob"];
                    r.Dob = dobDT.Day + "-" + dobDT.Month + "-" + dobDT.Year;
                    r.Gender = (string)dr["Gender"];
                    r.Address = (string)dr["Address"];
                    r.Postal = (string)dr["Postal"];
                    r.Ethnicity = (string)dr["Ethnicity"];
                    if (DBNull.Value != (object)dr["Description"])
                    {
                        r.Description = (string)dr["Description"];
                    }

                    result.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Volunteer RetrieveVolunteerById(int vid)
        {
            Volunteer v = new Volunteer();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Volunteer WHERE Volunteer_id=@vid";
                comm.Parameters.AddWithValue("@vid", vid);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    v.Volunteer_id = vid;
                    v.Nric = (string)dr["Nric"];
                    v.Name = (string)dr["Name"];
                    DateTime dobDT = (DateTime)dr["Dob"];
                    v.Dob = dobDT.Day + "-" + dobDT.Month + "-" + dobDT.Year;
                    v.Gender = (string)dr["Gender"];
                    v.Address = (string)dr["Address"];
                    v.Postal = (string)dr["Postal"];
                    v.Telephone = (string)dr["Telephone"];
                    v.Email = (string)dr["Email"];
                    v.Available_day = (string)dr["Available_day"];
                    v.Postal = (string)dr["Postal"];
                    if (DBNull.Value != (object)dr["Description"])
                    {
                        v.Description = (string)dr["Description"];
                    }
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return v;
        }

        public static ArrayList RetrieveVolunteersByName(string n)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Volunteer WHERE Name LIKE @name";
                comm.Parameters.AddWithValue("@name", "%" + n + "%");

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Volunteer v = new Volunteer();
                    v.Volunteer_id = (int)dr["volunteer_id"];
                    v.Nric = (string)dr["Nric"];
                    v.Name = (string)dr["Name"];
                    DateTime dobDT = (DateTime)dr["Dob"];
                    v.Dob = dobDT.Day + "-" + dobDT.Month + "-" + dobDT.Year;
                    v.Gender = (string)dr["Gender"];
                    v.Address = (string)dr["Address"];
                    v.Postal = (string)dr["Postal"];
                    v.Telephone = (string)dr["Telephone"];
                    v.Email = (string)dr["Email"];
                    v.Available_day = (string)dr["Available_day"];
                    if (DBNull.Value != (object)dr["Description"])
                    {
                        v.Description = (string)dr["Description"];
                    }

                    result.Add(v);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ShoppingList RetrieveShoppingListById(int lid)
        {
            ShoppingList sl = new ShoppingList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM ShoppingList WHERE List_id=@lid";
                comm.Parameters.AddWithValue("@lid", lid);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    sl.List_id = lid;
                    sl.Nric = (string)dr["Nric"];
                    if (DBNull.Value != (object)dr["Volunteer_id"])
                    {
                        sl.Volunteer_id = (int)dr["Volunteer_id"];
                    }
                    DateTime dateDT = (DateTime)dr["Date"];
                    sl.Date = dateDT.Day + "-" + dateDT.Month + "-" + dateDT.Year;
                    if (DBNull.Value != (object)dr["Date_req"])
                    {
                        DateTime datereqDT = (DateTime)dr["Date_req"];
                        sl.Date_req = datereqDT.Day + "-" + datereqDT.Month + "-" + datereqDT.Year;
                    }
                    if (DBNull.Value != (object)dr["Date_pur"])
                    {
                        DateTime datepurDT = (DateTime)dr["Date_pur"];
                        sl.Date_pur = datepurDT.Day + "-" + datepurDT.Month + "-" + datepurDT.Year;
                    }
                    if (DBNull.Value != (object)dr["Date_del"])
                    {
                        DateTime datedelDT = (DateTime)dr["Date_del"];
                        sl.Date_del = datedelDT.Day + "-" + datedelDT.Month + "-" + datedelDT.Year;
                    }
                    sl.Status = (string)dr["Status"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return sl;
        }

        public static ArrayList RetrieveItemsByName(string n)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Item WHERE Item_name LIKE @item_name";
                comm.Parameters.AddWithValue("@item_name", "%" + n + "%");

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Item i = new Item();
                    i.Item_name = (string)dr["Item_name"];
                    i.Price = Convert.ToDouble(dr["Price"].ToString());
                    if (DBNull.Value != (object)dr["Description"])
                    {
                        i.Description = (string)dr["Description"];
                    }

                    result.Add(i);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Item RetrieveItemByName(string n)
        {
            Item i = new Item();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Item WHERE Item_name=@item_name";
                comm.Parameters.AddWithValue("@item_name", n);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    i.Item_name = (string)dr["Item_name"];
                    i.Price = Convert.ToDouble(dr["Price"].ToString());
                    if (DBNull.Value != (object)dr["Description"])
                    {
                        i.Description = (string)dr["Description"];
                    }
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return i;
        }

        public static ArrayList RetrieveItemListByListId(int lid)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM ItemList WHERE List_id=@lid";
                comm.Parameters.AddWithValue("@lid", lid);

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    ItemList il = new ItemList();
                    il.List_id = (int)dr["List_id"];
                    il.Item_name = (string)dr["Item_name"];
                    il.Qty = (int)dr["Qty"];

                    result.Add(il);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }


        // Retrieve (Login)
        public static bool Login(string u, string p)
        {
            bool status = false;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM SocialWorker WHERE Username=@user AND Password=@pass";
                comm.Parameters.AddWithValue("@user", u);
                comm.Parameters.AddWithValue("@pass", p);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    status = true;
                }
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return status;
        }


        // Retrieve (Validity)
        public static bool CheckUsernameValidity(string u)
        {
            bool valid = true;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM SocialWorker WHERE Username=@user";
                comm.Parameters.AddWithValue("@user", u);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    valid = false;
                }
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return valid;
        }

        public static bool CheckResidentValidity(string n)
        {
            bool valid = true;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Resident WHERE Nric=@nric";
                comm.Parameters.AddWithValue("@nric", n);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    valid = false;
                }
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return valid;
        }

        public static bool CheckItemValidity(string n)
        {
            bool valid = true;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Item WHERE Item_name=@itemName";
                comm.Parameters.AddWithValue("@itemName", n);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    valid = false;
                }
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return valid;
        }


        // Retrieve (Session)
        public static SocialWorker GetUserSession(string u)
        {
            SocialWorker result = null;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM SocialWorker WHERE Username=@user";
                comm.Parameters.AddWithValue("@user", u);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    SocialWorker sw = new SocialWorker();
                    sw.Sw_id = (int)dr["Sw_id"];
                    sw.Username = u;
                    sw.Password = (string)dr["Password"];
                    sw.Name = (string)dr["Name"];
                    DateTime dobDT = (DateTime)dr["Dob"];
                    sw.Dob = dobDT.Day + "-" + dobDT.Month + "-" + dobDT.Year;
                    sw.Gender = (string)dr["Gender"];
                    sw.Email = (string)dr["Email"];
                    DateTime jdDT = (DateTime)dr["Joined_date"];
                    sw.Joined_date = jdDT.Day + "-" + jdDT.Month + "-" + jdDT.Year;

                    result = sw;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }


        // Retrieve (New Shopping List)
        public static int RetrieveNewShoppingListId()
        {
            int result = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT DISTINCT * FROM ShoppingList ORDER BY List_id DESC";

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    result = (int)dr["List_id"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }


        // Retrieve (Same Postal Sector)
        public static ArrayList RetrieveVolunteerIdsByPostal(string p)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                int postal = Convert.ToInt32(p);
                string parameter = "";

                if (postal >= 1 && postal <= 30)
                {
                    for (int i = 1; i <= 30; i++)
                    {
                        parameter += "Postal LIKE '" + string.Format("{0:00}", i) + "%'";
                        if (i < 30)
                        {
                            parameter += " OR ";
                        }
                    }
                }
                else if ((postal >= 31 && postal <= 57) || postal == 81 || postal == 82)
                {
                    for (int i = 31; i <= 57; i++)
                    {
                        parameter += "Postal LIKE '" + i + "%' OR ";
                    }
                    parameter += "Postal LIKE '81%' OR Postal LIKE '82%'";
                }
                else if (postal >= 58 && postal <= 71)
                {
                    for (int i = 58; i <= 71; i++)
                    {
                        parameter += "Postal LIKE '" + i + "%'";
                        if (i < 71)
                        {
                            parameter += " OR ";
                        }
                    }
                }
                else if ((postal >= 75 && postal <= 80) || postal == 72 || postal == 73)
                {
                    for (int i = 75; i <= 80; i++)
                    {
                        parameter += "Postal LIKE '" + i + "%' OR ";
                    }
                    parameter += "Postal LIKE '72%' OR Postal LIKE '73%'";
                }

                comm.CommandText = "SELECT * FROM Volunteer WHERE " + parameter;

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    result.Add((int)dr["volunteer_id"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }


        // Update
        public static int UpdateProfile(SocialWorker sw)
        {
            int rowupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE SocialWorker SET Name=@name, Email=@email, Dob=@dob, Gender=@gender WHERE Sw_id=@sid";

                comm.Parameters.AddWithValue("@name", sw.Name);
                comm.Parameters.AddWithValue("@email", sw.Email);
                comm.Parameters.AddWithValue("@dob", sw.Dob);
                comm.Parameters.AddWithValue("@gender", sw.Gender);
                comm.Parameters.AddWithValue("@sid", sw.Sw_id);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }

        public static int UpdateResident(Resident r)
        {
            int rowupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Resident SET Name=@name, Dob=@dob, Gender=@gender, Address=@address, Postal=@postal," +
                    "Ethnicity=@ethnicity, Description=@description WHERE Nric=@nric";

                comm.Parameters.AddWithValue("@nric", r.Nric);
                comm.Parameters.AddWithValue("@name", r.Name);
                comm.Parameters.AddWithValue("@dob", r.Dob);
                comm.Parameters.AddWithValue("@gender", r.Gender);
                comm.Parameters.AddWithValue("@address", r.Address);
                comm.Parameters.AddWithValue("@postal", r.Postal);
                comm.Parameters.AddWithValue("@ethnicity", r.Ethnicity);
                if (!string.IsNullOrEmpty(r.Description))
                {
                    comm.Parameters.AddWithValue("@description", r.Description);
                }
                else
                {
                    comm.Parameters.AddWithValue("@description", DBNull.Value);
                }
                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }

        public static int UpdateShoppingList(ShoppingList sl)
        {
            int rowupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE ShoppingList SET Volunteer_id=@vid, Date_req=@datereq, Date_pur=@datepur, Date_del=@datedel," +
                    "Status=@status WHERE List_id=@lid";

                comm.Parameters.AddWithValue("@lid", sl.List_id);
                comm.Parameters.AddWithValue("@vid", sl.Volunteer_id);
                comm.Parameters.AddWithValue("@datereq", sl.Date_req);
                if (!string.IsNullOrEmpty(sl.Date_pur))
                {
                    comm.Parameters.AddWithValue("@datepur", sl.Date_pur);
                }
                else
                {
                    comm.Parameters.AddWithValue("@datepur", DBNull.Value);
                }
                if (!string.IsNullOrEmpty(sl.Date_del))
                {
                    comm.Parameters.AddWithValue("@datedel", sl.Date_del);
                }
                else
                {
                    comm.Parameters.AddWithValue("@datedel", DBNull.Value);
                }
                comm.Parameters.AddWithValue("@status", sl.Status);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }

        public static int UpdateVolunteer(Volunteer v)
        {
            int rowupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Volunteer SET Nric=@nric, Name=@name, Dob=@dob, Gender=@gender, Address=@address, Postal=@postal," +
                    "Telephone=@telephone, Email=@email, Available_day=@available_day, Description=@description WHERE Volunteer_id=@vid";

                comm.Parameters.AddWithValue("@nric", v.Nric);
                comm.Parameters.AddWithValue("@name", v.Name);
                comm.Parameters.AddWithValue("@dob", v.Dob);
                comm.Parameters.AddWithValue("@gender", v.Gender);
                comm.Parameters.AddWithValue("@address", v.Address);
                comm.Parameters.AddWithValue("@postal", v.Postal);
                comm.Parameters.AddWithValue("@telephone", v.Telephone);
                comm.Parameters.AddWithValue("@email", v.Email);
                comm.Parameters.AddWithValue("@available_day", v.Available_day);
                if (!string.IsNullOrEmpty(v.Description))
                {
                    comm.Parameters.AddWithValue("@description", v.Description);
                }
                else
                {
                    comm.Parameters.AddWithValue("@description", DBNull.Value);
                }
                comm.Parameters.AddWithValue("@vid", v.Volunteer_id);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }

        public static int UpdateItem(Item i)
        {
            int rowupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Item SET Description=@description, Price=@price WHERE Name=@name";

                comm.Parameters.AddWithValue("@price", i.Price);
                if (!string.IsNullOrEmpty(i.Description))
                {
                    comm.Parameters.AddWithValue("@description", i.Description);
                }
                else
                {
                    comm.Parameters.AddWithValue("@description", DBNull.Value);
                }
                comm.Parameters.AddWithValue("@name", i.Item_name);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;

        }


        // Delete
        public static int DeleteAccount(int sid)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM SocialWorker WHERE Sw_id=@sid";

                comm.Parameters.AddWithValue("@sid", sid);

                rowsdeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsdeleted;
        }

        public static int DeleteResident(string n)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Resident WHERE Nric=@nric";

                comm.Parameters.AddWithValue("@nric", n);

                rowsdeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsdeleted;
        }

        public static int DeleteVolunteer(int vid)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Volunteer WHERE Volunteer_id=@vid";
                comm.Parameters.AddWithValue("@vid", vid);

                rowsdeleted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return rowsdeleted;
        }

        public static int DeleteShoppingList(int lid)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM ShoppingList WHERE List_id=@lid";

                comm.Parameters.AddWithValue("@lid", lid);

                rowsdeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsdeleted;
        }

        public static int DeleteAllItemList(int lid)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM ItemList WHERE List_id=@lid";
                comm.Parameters.AddWithValue("@lid", lid);

                rowsdeleted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return rowsdeleted;
        }

        public static int DeleteItem(string n)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["LMKJConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Item WHERE Item_name=@itemName";

                comm.Parameters.AddWithValue("@itemName", n);

                rowsdeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsdeleted;
        }
    }
}
